#/bin/bash

charset='--default-character-set=utf8'
db='monitor'
tableSuffix='_20131115'
pNameArr=('青春迪士尼包月' '宝贝迪士尼包月')

for i in {0..2}
do
  echo "[info] calculating for ${pNameArr[$i]}...."
  pname=${pNameArr[$i]}
  mysql -uroot -pmysql -h127.0.0.1 $charset $db -e"
  select t1.*, t2.order_count_bmm, t2.order_count_bmm/t1.order_count_total from 
    (select date_format(subscribetime,'%Y-%m-%d') day, count(*) order_count_total from order_ppv$tableSuffix where servicename='$pname' group by date_format(subscribetime, '%Y-%m-%d')) t1 left join
    (select date_format(subscribetime,'%Y-%m-%d') day, count(distinct o.orderid) order_count_bmm from order_ppv$tableSuffix o, newclicklog$tableSuffix c where o.servicename='$pname' and c.toprogram='$pname' and o.userid=c.userid and abs(timestampdiff(SECOND, c.clicktime, o.subscribetime))<=3600*24 group by date_format(o.subscribetime, '%Y-%m-%d')) t2
  on t1.day=t2.day; "
done

echo "[info] total result..."
mysql -uroot -pmysql -h127.0.0.1 $charset $db -e"
 select t1.*, t2.order_count_bmm, t2.order_count_bmm/t1.order_count_total from 
    (select date_format(subscribetime,'%Y-%m-%d') day, count(*) order_count_total from order_ppv_20131115 group by date_format(subscribetime, '%Y-%m-%d')) t1 left join
    (select date_format(subscribetime,'%Y-%m-%d') day, count(distinct o.orderid) order_count_bmm from order_ppv_20131115 o, newclicklog_20131115 c where o.servicename=c.toprogram and o.userid=c.userid and timestampdiff(SECOND, c.clicktime, o.subscribetime)<=600 group by date_format(o.subscribetime, '%Y-%m-%d')) t2
  on t1.day=t2.day;"

